﻿using System;
using System.Collections.Generic;
using System.Linq;

namespace IntellectShop.Common
{
	public partial class Category
	{
		public static List<Common.Category> RetrieveAll()
		{
			using (DB.IntellectShopDataContext context = new DB.IntellectShopDataContext(Globals.ConnectionString))
			{
				var query =
							from category in context.Categories
							orderby category.SortingID descending
							select category;

				List<Common.Category> result = new List<Category>();
				foreach (var row in query)
				{
					result.Add(new Common.Category
					{
						CategoryID = row.CategoryID,
						Name = row.Name,
						SortingID = row.SortingID
					});
				}

				return result;
			}
		}

		public static Common.Category RetrieveByID(int categoryID)
		{
			using (DB.IntellectShopDataContext context = new DB.IntellectShopDataContext(Globals.ConnectionString))
			{
				var queryCategory = context.Categories.SingleOrDefault(category => category.CategoryID == categoryID);
				return new Common.Category { CategoryID = queryCategory.CategoryID, Name = queryCategory.Name, SortingID = queryCategory.SortingID };
			}
		}

		public static Common.Category RetrieveByName(string name)
		{
			using (DB.IntellectShopDataContext context = new DB.IntellectShopDataContext(Globals.ConnectionString))
			{
				var queryCategory = context.Categories.SingleOrDefault(category => category.Name == name);
				if (queryCategory == null)
				{
					return null;
				}

				return new Common.Category { CategoryID = queryCategory.CategoryID, Name = queryCategory.Name, SortingID = queryCategory.SortingID };
			}
		}

		public void Create(string createdBy)
		{
			using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
			{
				var categoryDB = new DB.Category
				{
					Name = this.Name,
					SortingID = this.SortingID,
					Created = DateTime.Now,
					CreatedBy = createdBy
				};
				dataContext.Categories.InsertOnSubmit(categoryDB);
				dataContext.SubmitChanges();

				this.CategoryID = categoryDB.CategoryID;
			}
		}

		public void Update(string updatedBy)
		{
			using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
			{
				var categoryDB = dataContext.Categories.SingleOrDefault(x => x.CategoryID == this.CategoryID);
				categoryDB.Name = this.Name;
				categoryDB.SortingID = this.SortingID;
				categoryDB.Updated = DateTime.Now;
				categoryDB.UpdatedBy = updatedBy;
				dataContext.SubmitChanges();
			}
		}

		public static void Delete(int categoryID)
		{
			using (DB.IntellectShopDataContext dataContext = new DB.IntellectShopDataContext(Common.Globals.ConnectionString))
			{
				var categoryDB = (from category in dataContext.Categories where category.CategoryID == categoryID select category).SingleOrDefault();

				dataContext.Categories.DeleteOnSubmit(categoryDB);
				dataContext.SubmitChanges();
			}
		}
	}
}
